import os
import sys
script_dir =os.path.dirname(os.path.abspath(__file__))
sys.path.append(script_dir)
import pymysql
import pandas as pd
import asyncio
import time
import datetime
import traceback
import os
import sys
# print(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
# # 添加导包路径
# sys.path.append(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
sys.path.append(os.path.split(os.path.split(sys.path[0])[0])[0])

from bin.openapi import OpenApiBase
from sqlalchemy import create_engine

from loguru import logger
logger.add(r"D:\my_work\ac_lingxing_api\ac_lingxing_api\new_ad\basic_data\ad_sp.log")

class ad_father:

    def __init__(self):
        self.op_api = OpenApiBase("https://openapi.lingxing.com", "ak_A77HkbumPXlk3", "6z+ZCTX2z5ctgITSuH+akQ==") 
        self.access_token = ""
        self.refresh_token = ""

    async def generate_access_token(self):
        resp = await self.op_api.generate_access_token()
        self.access_token = resp.access_token
        self.refresh_token = resp.refresh_token
        assert resp.access_token
        # return resp.dict()
    
    async def refresh_token_handler(self):
        resp = await self.op_api.refresh_token(self.refresh_token)
        self.refresh_token =  resp.refresh_token
        self.access_token = resp.access_token
        assert resp.access_token

    async def check_token(self, data):
        if data['code'] == 0:
            return True
        elif data['code'] in [2001005, 2001003, 2001008, 2001009]:
            await self.generate_access_token()
            return False
        else:
            logger.info(f"code:{ data['code']}")
            return False
    
    
class db_tools:
    def __init__(self):
        self.engine = create_engine('mysql+pymysql://user_rpa:123456@192.168.8.10:3306/ad_api?charset=utf8mb4')   # 西安数据库
        self.conn = pymysql.connect(user='user_rpa', password='123456', host='192.168.8.10', database='ad_api') # 西安数据库
        # self.engine = create_engine('mysql+pymysql://root:123456@192.168.8.200:3306/new_ad?charset=utf8mb4')  # 河津数据库
        # self.conn = pymysql.connect(user='root', password='123456', host='192.168.8.200', database='new_ad') # 河津数据库
        
        self.cur = self.conn.cursor()
    
    def insert_data(self, data,USER_TABLE_NAME):
        data.to_sql(USER_TABLE_NAME, con=self.engine, index=False, if_exists="append")
    


class tools:

    @staticmethod
    def get_sid_accountname(data):
        sid = data['lx_sid']
        account_name = data['store_name']
        return sid, account_name
    @staticmethod
    def get_date_lst(start_date, end_date):
        start_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
        end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
        date_lst = []
        while start_date != end_date:
            date_lst.append(start_date.strftime("%Y-%m-%d"))
            start_date = start_date+datetime.timedelta(days=-1)
        return date_lst
    @staticmethod
    def get_engine(database):
        engine = create_engine(f'mysql+pymysql://user1:123456@192.168.8.10:3306/{database}?charset=utf8mb4')
        return engine
    @staticmethod
    def get_cur(database):
        conn = pymysql.connect(user='user1', password='123456', host='192.168.8.10', database=database)
        cur = conn.cursor()
        return conn, cur

class ad_sp(ad_father):

    def __init__(self):
        super().__init__()
        self.db = db_tools()
        self.headers = {
            "X-API-VERSION":"2"
        }

    def get_account(self, country_lst):
        country_str = ",".join(["'" + str(country) + "'" for country in country_lst])
        sql = f"select lx_sid, lx_store_name, store_name, lx_country from store_info where lx_country in ({country_str}) and lx_status = 1 and lx_has_ads_setting = 1"
        engine = tools.get_engine("ac_base_data")
        data_account = pd.read_sql(sql, con=engine)
        data_account = data_account.drop_duplicates()
        logger.info(data_account)
        return data_account
    
    async def sp_handler(self, url, method, req_body, report_date, reports_name, account):
        logger.info(f"{report_date} 日期{reports_name}数据开始抓取")
        res_lst = []
        while 1:
            try:
                res = await self.op_api.request(self.access_token, url, method, req_body=req_body, headers=self.headers)
            except:
                logger.error("异常：", traceback.format_exc())
                # print(self.access_token, url, method, req_body)
                # time.sleep(10)
                continue
            resp = res.dict()
            # print(resp)
            if not await self.check_token(resp):
                continue
            # print((resp))
            if len(resp['data']) == 0:
                break
            res_lst = res_lst + resp['data']
            req_body['offset'] = req_body['offset'] + 100
        if len(res_lst) == 0:
            return []
        res_df = pd.DataFrame(res_lst)
        res_df['sid'] = req_body['sid']
        res_df['account'] = account
        res_df['update_time'] = pd.to_datetime(datetime.datetime.now().strftime("%Y-%m-%d"))
        res_df['creation_date'] = res_df['creation_date'].apply(lambda a: datetime.datetime.fromtimestamp(a/1000))
        res_df['last_updated_date'] = res_df['last_updated_date'].apply(lambda a: datetime.datetime.fromtimestamp(a/1000))
        
        return res_df

    async def sp_portfolios_data(self, sid, report_date, account):
        url = "/pb/openapi/newad/portfolios"
        method = "POST"
        req_body = {
                    "sid": sid,
                    "offset": 0,
                    "length": 100
            }
        printer_info = "portfolios_reports"
        res_df = await self.sp_handler(url, method, req_body, report_date, printer_info, account)

        if len(res_df)>0:
            table_name = "sp_portfolios_data_api"
            self.db.insert_data(res_df, table_name)
            logger.info(f"{report_date} 日期{printer_info}数据已插入")
        else:
            logger.info("无数据")

    async def sp_spCampaigns_data(self, sid, report_date, account):
        url = "/pb/openapi/newad/spCampaigns"
        method = "POST"
        req_body = {
                    "sid": sid,
                    "offset": 0,
                    "length": 100
            }
        printer_info = "spCampaigns_data"
        res_df = await self.sp_handler(url, method, req_body, report_date, printer_info, account)

        if len(res_df)>0:
            table_name = "sp_campaigns_data_api"
            self.db.insert_data(res_df, table_name)
            # self.db.replace_data(res_df, table_name, report_date, account)
            logger.info(f"{report_date} 日期{printer_info}数据已插入")
        else:
            logger.info("无数据")

    async def sp_spAdGroups_data(self, sid, report_date, account):
        url = "/pb/openapi/newad/spAdGroups"
        method = "POST"
        req_body = {
                    "sid": sid,
                    "offset": 0,
                    "length": 100
            }
        printer_info = "spAdGroups_data"
        res_df = await self.sp_handler(url, method, req_body, report_date, printer_info, account)

        if len(res_df)>0:
            table_name = "sp_ad_groups_data_api"
            self.db.insert_data(res_df, table_name)
            # self.db.replace_data(res_df, table_name, report_date, account)
            logger.info(f"{report_date} 日期{printer_info}数据已插入")
        else:
            logger.info("无数据")

    async def sp_spProductAds_data(self, sid, report_date, account):
        url = "/pb/openapi/newad/spProductAds"
        method = "POST"
        req_body = {
                    "sid": sid,
                    "offset": 0,
                    "length": 100
            }
        printer_info = "spProductAds_data"
        res_df = await self.sp_handler(url, method, req_body, report_date, printer_info, account)

        if len(res_df)>0:
            table_name = "sp_product_ads_data_api"
            self.db.insert_data(res_df, table_name)
            # self.db.replace_data(res_df, table_name, report_date, account)
            logger.info(f"{report_date} 日期{printer_info}数据已插入")
        else:
            logger.info("无数据")

    async def sp_spKeywords_data(self, sid, report_date, account):
        url = "/pb/openapi/newad/spKeywords"
        method = "POST"
        req_body = {
                    "sid": sid,
                    "offset": 0,
                    "length": 100
            }
        printer_info = "spKeywords_data"
        res_df = await self.sp_handler(url, method, req_body, report_date, printer_info, account)

        if len(res_df)>0:
            table_name = "sp_keywords_data_api"
            self.db.insert_data(res_df, table_name)
            # self.db.replace_data(res_df, table_name, report_date, account)
            logger.info(f"{report_date} 日期{printer_info}数据已插入")
        else:
            logger.info("无数据")

    async def sp_spTargets_data(self, sid, report_date, account):
        url = "/pb/openapi/newad/spTargets"
        method = "POST"
        req_body = {
                    "sid": sid,
                    "offset": 0,
                    "length": 100
            }
        printer_info = "spTargets_data"
        res_df = await self.sp_handler(url, method, req_body, report_date, printer_info, account)

        if len(res_df)>0:
            table_name = "sp_targets_data_api"
            self.db.insert_data(res_df, table_name)
            # self.db.replace_data(res_df, table_name, report_date, account)
            logger.info(f"{report_date} 日期{printer_info}数据已插入")
        else:
            logger.info("无数据")

    async def sp_spNegativeTargetsOrKeywords_data(self, sid, report_date, account):
        url = "/pb/openapi/newad/spNegativeTargetsOrKeywords"
        method = "POST"
        req_body = {
                    "sid": sid,
                    "offset": 0,
                    "length": 100,
                    "target_type":''
            }
        printer_info = "spNegativeTargetsOrKeywords_data"
        res_df = await self.sp_handler(url, method, req_body, report_date, printer_info, account)

        if len(res_df)>0:
            table_name = "sp_negative_target_data_api"
            self.db.insert_data(res_df, table_name)
            # self.db.replace_data(res_df, table_name, report_date, account)
            logger.info(f"{report_date} 日期{printer_info}数据已插入")
        else:
            logger.info("无数据")


async def sp_data_handler(semaphore, hanlder, report_date, sid, account_name):
    async with semaphore:
        await hanlder(sid, report_date, account_name)

async def main():
    # date_lst = tools.get_date_lst("2025-03-03","2025-03-02")

    ass = ad_sp()


    date_url = ["sp_ad_groups_data_api", "sp_campaigns_data_api", "sp_keywords_data_api", "sp_portfolios_data_api", "sp_product_ads_data_api", "sp_targets_data_api"]
    str = []
    day_date = (datetime.datetime.now()).strftime("%Y-%m-%d")

    con, cur = tools.get_cur("ad_api")
    for USER_TABLE_NAME in date_url:
        sql = f"""DELETE FROM {USER_TABLE_NAME} WHERE (DATE(update_time) = '{day_date}' ) ;"""
        cur.execute(sql)
    cur.close()
    con.commit()
    await ass.generate_access_token()
    tasks = []
    account_data = ass.get_account(["美国", '英国', '法国', '德国'])
    sp_portfolios_data_semaphore = asyncio.Semaphore(10)
    sp_spCampaigns_data_semaphore = asyncio.Semaphore(10)
    sp_spAdGroups_data_semaphore = asyncio.Semaphore(10)
    sp_spProductAds_data_semaphore = asyncio.Semaphore(10)
    sp_spKeywords_data_semaphore = asyncio.Semaphore(10)
    sp_spTargets_data_semaphore = asyncio.Semaphore(10)
    sp_spNegativeTargetsOrKeywords_data_semaphore = asyncio.Semaphore(10)
    report_date = datetime.datetime.now().strftime("%Y-%m-%d")
    for i, data in account_data.iterrows():
        sid, account_name = tools.get_sid_accountname(data)
        tasks.append(sp_data_handler(sp_portfolios_data_semaphore, ass.sp_portfolios_data, report_date, sid, account_name))
        tasks.append(sp_data_handler(sp_spCampaigns_data_semaphore, ass.sp_spCampaigns_data, report_date, sid, account_name))
        tasks.append(sp_data_handler(sp_spAdGroups_data_semaphore, ass.sp_spAdGroups_data, report_date, sid, account_name))
        tasks.append(sp_data_handler(sp_spProductAds_data_semaphore, ass.sp_spProductAds_data, report_date, sid, account_name))
        tasks.append(sp_data_handler(sp_spKeywords_data_semaphore, ass.sp_spKeywords_data, report_date, sid, account_name))
        tasks.append(sp_data_handler(sp_spTargets_data_semaphore, ass.sp_spTargets_data, report_date, sid, account_name))
        # tasks.append(sp_data_handler(sp_spNegativeTargetsOrKeywords_data_semaphore, ass.sp_spNegativeTargetsOrKeywords_data, report_date, sid, account_name))

    result = await asyncio.gather(*tasks)



def run():
    start = time.time()
    asyncio.run(main())
    end = time.time()
    logger.info(f"共耗时: {end-start}")



run()
